package com.hp.gxw.controller;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.hp.gxw.cache.BaseCache;
import com.hp.gxw.config.DataBaseConfig;
import com.hp.gxw.constant.LayTableR;
import com.hp.gxw.constant.ReportEnum;
import com.hp.gxw.service.IReportService;
import com.hp.gxw.utils.*;
import com.hp.gxw.vo.TableColsVO;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;
import java.util.concurrent.TimeUnit;
import java.util.stream.Collectors;

@Controller
@RequestMapping("/manage/report")
@Slf4j
public class ReportController extends SelectListController{

    @Autowired
    private IReportService iReportService;

    @Autowired
    private BaseCache baseCache;

    @RequestMapping("/completed_index")
    public String completed_index(Model model){
        model.addAttribute("customer",customerList());
        model.addAttribute("product",productList());
        model.addAttribute("process",processList("加工"));
        return "report/completed_report/index";
    }
    @RequestMapping("/sale_settle_index")
    public String sale_settle_index(Model model){
        return "report/sale_settle_report/index";
    }

    @RequestMapping("/production_index")
    public String production_index(Model model){
        model.addAttribute("customer",customerList());
        model.addAttribute("product",productList());
        model.addAttribute("process",processList("加工"));
        return "report/production_report/index";
    }

    @PostMapping("/loadCols")
    @ResponseBody
    public Object loadCols(String month, String type) throws Exception {
        Map<String, Object> map = new HashMap<>();
        Object obj = baseCache.get("report_" + type + "_" + month);
        if(obj != null){
            map = JSON.parseObject(obj.toString(), Map.class);
        }else{
            List<TableColsVO> fistCols = new ArrayList<>();
            fistCols.add(new TableColsVO("numbers","序号",null,2,60));
            fistCols.add(new TableColsVO("销售订单","sale_order",2,140));
            fistCols.add(new TableColsVO("客户编码","customer_code",2,90));
            fistCols.add(new TableColsVO("客户名称","customer_name",2,130));
            fistCols.add(new TableColsVO("随工单","work_order",2,140));
            fistCols.add(new TableColsVO("产品编码","cinvcode",2,100));
            fistCols.add(new TableColsVO("产品名称","cinvname",2,100));
            fistCols.add(new TableColsVO("工序编码","process_code",2,90));
            fistCols.add(new TableColsVO("工序名称","process_name",2,140));
            fistCols.add(new TableColsVO("加工数量","qty",2,90));
            if(iReportService.checkTableExist(month.replace("-",""),type) < 1){
                map.put("first",fistCols);
                return map;
            }
            List<TableColsVO> secondCols = new ArrayList<>();
            List<Map<String, Object>> colsList = iReportService.getCols(month.replace("-",""),type);
            //按成本要素类型分类
            Map<String, List<Map<String, Object>>> collect = colsList.stream().collect(Collectors.groupingBy(e -> (e.get("name")==null?"":e.get("name").toString())));
            Set<Map.Entry<String, List<Map<String, Object>>>> entries = collect.entrySet();
            for (Map.Entry<String, List<Map<String, Object>>> entry : entries) {
                if(entry.getValue().size() == 1){
                    if(StringUtils.isEmpty(entry.getKey())){
                        fistCols.add(new TableColsVO(null,"折旧、租赁<br/>"+entry.getValue().get(0).get("element_name").toString(),"filed_"+entry.getValue().get(0).get("element_code").toString(),2,150));
                    }else{
                        fistCols.add(new TableColsVO(null,entry.getKey()+"<br/>"+entry.getValue().get(0).get("element_name").toString(),"filed_"+entry.getValue().get(0).get("element_code").toString(),2,150));
                    }
                }else{
                    if(StringUtils.isEmpty(entry.getKey())){
                        fistCols.add(new TableColsVO("折旧、租赁",entry.getValue().size()));
                    }else{
                        fistCols.add(new TableColsVO(entry.getKey(),entry.getValue().size()));
                    }
                    for (Map<String, Object> valueMap : entry.getValue()) {
                        secondCols.add(new TableColsVO(valueMap.get("element_name").toString(),"filed_"+valueMap.get("element_code").toString(), 150));
                    }
                }
            }
            map.put("first",fistCols);
            map.put("second",secondCols);
            baseCache.add("report_" + type + "_" + month, JSONObject.toJSONString(map),15, TimeUnit.MINUTES);
        }
        return map;
    }

    @PostMapping("/list/{month}/{type}")
    @ResponseBody
    public LayTableR list(@PathVariable("month") String month, @PathVariable("type") String type, Integer page, Integer limit, String master) throws Exception {
        QueryWrapper<Object> ew = ConditionUtil.convert(master, new QueryWrapper<Object>());
        if(iReportService.checkTableExist(month.replace("-",""),type) < 1){
            return LayTableR.result(0, 0, new ArrayList<>());
        }
        Page<Map<String, Object>> pageList = iReportService.getCompletedOrProductionData(month.replace("-",""), type, new Page<>(page,limit), ew);
        for (Map<String, Object> record : pageList.getRecords()) {
            String element_info = record.get("element_info") == null ? "" : record.get("element_info").toString();
            String[] split = element_info.split(",");
            for (String s : split) {
                String[] one = s.split(":");
                record.put("filed_"+one[0],one[1]);
            }
            record.remove("element_info");
        }
        return LayTableR.result(0, pageList.getTotal(), pageList.getRecords());
    }

    @PostMapping("/all/list")
    @ResponseBody
    public LayTableR allList(Integer page, Integer limit, String master) throws Exception {
        QueryWrapper<Object> ew = ConditionUtil.convert(master, new QueryWrapper<>());
        Page<cn.hutool.json.JSONObject> pageList = iReportService.getSaleSettlePage(new Page<>(page,limit), ew);
        //按settle_erp_code分组
        Map<String, List<cn.hutool.json.JSONObject>> collect = pageList.getRecords().stream().collect(Collectors.groupingBy(e -> e.getStr("settle_erp_code")));
        Set<Map.Entry<String, List<cn.hutool.json.JSONObject>>> entries = collect.entrySet();
        for (Map.Entry<String, List<cn.hutool.json.JSONObject>> entry : entries) {
            //查询发货单对应的发票信息以及收款信息
            Statement statement = null;
            ResultSet resultSet = null;
            try {
                String sql = "select top 1 b.cSBVCode,b.dDate,b.cDefine12,b.cDefine13,b.cDefine16,a.iMoneySum,a.iSum " +
                        "from SaleBillVouchs a inner join SaleBillVouch b on a.SBVID=b.SBVID" +
                        " where a.cbdlcode='"+entry.getKey()+"'"+
                        "order by b.dDate desc";
                statement = DataBaseConfig.connection.createStatement();
                resultSet = statement.executeQuery(sql);
                while (resultSet.next()){
                    for (cn.hutool.json.JSONObject object : entry.getValue()) {
                        object.put("htbh",resultSet.getObject("cDefine12"));
                        object.put("htje",resultSet.getObject("cDefine16"));
                        object.put("htqdrq",resultSet.getObject("cDefine13"));
                        object.put("fph",resultSet.getObject("cSBVCode"));
                        object.put("fpje",resultSet.getObject("iSum"));
                        object.put("kprq",resultSet.getObject("dDate"));
                        object.put("ljskje",resultSet.getObject("iMoneySum"));
                    }
                }
            }catch (SQLException e){
                e.printStackTrace();
                log.error("查询账套数据异常:"+e);
            }finally {
                try {
                    if(statement != null){
                        statement.close();
                    }
                    if(resultSet != null){
                        resultSet.close();
                    }
                }catch (SQLException e){
                    e.printStackTrace();
                }

            }
        }
        return LayTableR.result(0, pageList.getTotal(), pageList.getRecords());
    }

    @RequestMapping("/download/{month}/{type}")
    @ResponseBody
    public void download(@PathVariable("month") String month, @PathVariable("type") String type, String condition, HttpServletResponse response) {
        List<Map> conditionList = JSON.parseArray(condition, Map.class);
        try {
            String queryCriteriaStr = "【查询条件】";
            QueryWrapper qw = new QueryWrapper();
            if(conditionList.size() > 0){
                for(Map m : conditionList){
                    queryCriteriaStr = queryCriteriaStr+m.get("title")+": "+m.get("value")+";   ";
                    switch (m.get("type").toString()){
                        case "equal":
                            qw.eq(m.get("field").toString(), m.get("value"));
                            break;
                        case "like":
                            qw.like(m.get("field").toString(), m.get("value"));
                            break;
                        case "unequal":
                            qw.ne(m.get("field").toString(), m.get("value"));
                            break;
                        case "empty":
                            qw.isNull(m.get("field").toString());
                            break;
                        case "notempty":
                            qw.isNotNull(m.get("field").toString());
                            break;
                        case "between":
                            qw.between(m.get("field").toString(), m.get("left"), m.get("right"));
                            break;
                        case "greater_than":
                            qw.gt(m.get("field").toString(), m.get("value"));
                            break;
                        case "less_than":
                            qw.lt(m.get("field").toString(), m.get("value"));
                            break;
                        case "start":
                            qw.likeLeft(m.get("field").toString(), m.get("value"));
                            break;
                        case "end":
                            qw.likeRight(m.get("field").toString(), m.get("value"));
                            break;
                    }
                }
            }else{
                queryCriteriaStr = queryCriteriaStr+"无查询条件，查询全表";
            }
            //excel表头
            List<TitleEntity> titleList=new ArrayList<>();
            titleList.add(new TitleEntity("0",null, ReportEnum.getMsg(type), null));
            titleList.add(new TitleEntity("1","0",queryCriteriaStr,null));
            titleList.add(new TitleEntity("2","1","销售订单","sale_order"));
            titleList.add(new TitleEntity("3","1","客户编码","customer_code"));
            titleList.add(new TitleEntity("4","1","客户名称","customer_name"));
            titleList.add(new TitleEntity("5","1","随工单","work_order"));
            titleList.add(new TitleEntity("6","1","产品编码","cinvcode"));
            titleList.add(new TitleEntity("7","1","产品名称","cinvname"));
            titleList.add(new TitleEntity("8","1","工序编码","process_code"));
            titleList.add(new TitleEntity("9","1","工序名称","process_name"));
            titleList.add(new TitleEntity("10","1","加工数量","qty"));
            if(iReportService.checkTableExist(month.replace("-",""),type) < 1){
                response.setContentType("text/html;charset=utf-8");
                response.getWriter().write("<script>alert('没有数据可导出');window.history.back();</script>");
                return;
            }
            List<Map<String, Object>> colsList = iReportService.getCols(month.replace("-",""),type);
            //按成本要素类型分类
            Map<String, List<Map<String, Object>>> collect = colsList.stream().collect(Collectors.groupingBy(e -> (e.get("name")==null?"":e.get("name").toString())));
            Set<Map.Entry<String, List<Map<String, Object>>>> entries = collect.entrySet();
            int act = 1;
            int bct = 1;
            for (Map.Entry<String, List<Map<String, Object>>> entry : entries) {
                if(StringUtils.isEmpty(entry.getKey())){
                    titleList.add(new TitleEntity("a000"+act,"1","折旧、租赁",null));
                }else{
                    titleList.add(new TitleEntity("a000"+act,"1",entry.getKey(),null));
                }
                for (Map<String, Object> valueMap : entry.getValue()) {
                    titleList.add(new TitleEntity("b000"+bct,"a000"+act,valueMap.get("element_name").toString(),"filed_"+valueMap.get("element_code").toString()));
                    bct++;
                }
                act++;
            }
            //excel数据
            List<Map<String, Object>> list = iReportService.getCompletedOrProductionDataList(month.replace("-",""), type, qw);
            for (Map<String, Object> record : list) {
                String element_info = record.get("element_info") == null ? "" : record.get("element_info").toString();
                String[] split = element_info.split(",");
                for (String s : split) {
                    String[] one = s.split(":");
                    record.put("filed_"+one[0],one[1]);
                }
                record.remove("element_info");
            }
            ExcelTool excelTool = new ExcelTool(ReportEnum.getMsg(type),20,20);
            List<Column>  titleData = excelTool.columnTransformer(titleList,"t_id","t_pid","t_content","t_fielName","0");
            response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(ReportEnum.getMsg(type)+ DateUtil.currentYYMMDD()+".xlsx", "utf-8"));
            excelTool.exportExcel(titleData,list, response.getOutputStream(),true,true);
        }catch (Exception e) {
            e.printStackTrace();
        }
    }

    @RequestMapping("/download/sale_settle")
    @ResponseBody
    public void downloadSale_settle(String condition, HttpServletResponse response) {
        List<Map> conditionList = JSON.parseArray(condition, Map.class);
        try {
            String queryCriteriaStr = "【查询条件】";
            QueryWrapper qw = new QueryWrapper();
            if(conditionList.size() > 0){
                for(Map m : conditionList){
                    queryCriteriaStr = queryCriteriaStr+m.get("title")+": "+m.get("value")+";   ";
                    switch (m.get("type").toString()){
                        case "equal":
                            qw.eq(m.get("field").toString(), m.get("value"));
                            break;
                        case "like":
                            qw.like(m.get("field").toString(), m.get("value"));
                            break;
                        case "unequal":
                            qw.ne(m.get("field").toString(), m.get("value"));
                            break;
                        case "empty":
                            qw.isNull(m.get("field").toString());
                            break;
                        case "notempty":
                            qw.isNotNull(m.get("field").toString());
                            break;
                        case "between":
                            qw.between(m.get("field").toString(), m.get("left"), m.get("right"));
                            break;
                        case "greater_than":
                            qw.gt(m.get("field").toString(), m.get("value"));
                            break;
                        case "less_than":
                            qw.lt(m.get("field").toString(), m.get("value"));
                            break;
                        case "start":
                            qw.likeLeft(m.get("field").toString(), m.get("value"));
                            break;
                        case "end":
                            qw.likeRight(m.get("field").toString(), m.get("value"));
                            break;
                    }
                }
            }else{
                queryCriteriaStr = queryCriteriaStr+"无查询条件，查询全表";
            }
            //excel表头
            List<TitleEntity> titleList=new ArrayList<>();
            titleList.add(new TitleEntity("0",null, "销售结算总表", null));
            titleList.add(new TitleEntity("1","0",queryCriteriaStr,null));
            titleList.add(new TitleEntity("2","1","市场专员","person_name"));
            titleList.add(new TitleEntity("3","1","业务类型·","ywlx"));
            titleList.add(new TitleEntity("4","1","客户公司","customer_name"));
            titleList.add(new TitleEntity("5","1","客户外采人员","purchase_person"));
            titleList.add(new TitleEntity("6","1","订单类别（研发、筛选测试（一筛）、筛选测试（二筛）、CP测试、生产、试验、打字、编带、借场地测试、借人测试、鉴定、 质量一致性、可靠性摸底试验、委外、购销、租赁）","type"));
            titleList.add(new TitleEntity("7","1","产品类别（单片、模块、晶圆、滤波器、硬件、软件等）","cplb"));
            titleList.add(new TitleEntity("8","1","内部订单号","order_no"));
            titleList.add(new TitleEntity("9","1","客户订单号","customer_order"));
            titleList.add(new TitleEntity("10","1","产品型号","cinvname"));
            titleList.add(new TitleEntity("11","1","客户批次","cbatch"));
            titleList.add(new TitleEntity("12","1","数量","order_qty"));
            titleList.add(new TitleEntity("13","1","有无实物","sfsw"));
            titleList.add(new TitleEntity("14","1","工序","order_process_name"));
            titleList.add(new TitleEntity("15","1","来料日期","llrq"));
            titleList.add(new TitleEntity("16","1","产品实际交付日期","dispatch_date"));
            titleList.add(new TitleEntity("17","1","出货数量","dispatch_qty"));
            titleList.add(new TitleEntity("18","1","合格数量","quality_qty"));
            titleList.add(new TitleEntity("19","1","不合格数量","unquality_qty"));
            titleList.add(new TitleEntity("20","1","库存数量","stock_qty"));
            titleList.add(new TitleEntity("21","1","在制数量","producting_qty"));
            titleList.add(new TitleEntity("22","1","关联订单",""));
            titleList.add(new TitleEntity("23","1","备注","memo"));
            titleList.add(new TitleEntity("24","1","特殊备注","special_memo"));
            titleList.add(new TitleEntity("25","1","计价单位","czjslx"));
            titleList.add(new TitleEntity("26","1","结算数值","settle_qty"));
            titleList.add(new TitleEntity("27","1","单价","ioritaxcost"));
            titleList.add(new TitleEntity("28","1","承揽金额","order_amount"));
            titleList.add(new TitleEntity("29","1","产出金额","settle_amount"));
            titleList.add(new TitleEntity("30","1","是否对账","account"));
            titleList.add(new TitleEntity("31","1","合同编号","htbh"));
            titleList.add(new TitleEntity("32","1","合同金额","htje"));
            titleList.add(new TitleEntity("33","1","签订合同日期","htqdrq"));
            titleList.add(new TitleEntity("34","1","发票号","fph"));
            titleList.add(new TitleEntity("35","1","开票金额","fpje"));
            titleList.add(new TitleEntity("36","1","开票日期","kprq"));
            titleList.add(new TitleEntity("37","1","累计收款金额","ljskje"));
            titleList.add(new TitleEntity("38","1","回款日期","hkrq"));
            titleList.add(new TitleEntity("39","1","承揽商","cls"));

            //excel数据
            List<Map<String, Object>> list = iReportService.getSaleSettleList(qw);
            ExcelTool excelTool = new ExcelTool("销售结算总表",20,20);
            List<Column>  titleData = excelTool.columnTransformer(titleList,"t_id","t_pid","t_content","t_fielName","0");
            response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode("销售结算总表"+ DateUtil.currentYYMMDD()+".xlsx", "utf-8"));
            excelTool.exportExcel(titleData,list, response.getOutputStream(),true,true);
        }catch (Exception e) {
            e.printStackTrace();
        }
    }
}


